﻿using System;
using System.Collections.Generic;
using System.Text;
using Entity;
using DataAccess;
using System.Data.OleDb;
using System.Data;

namespace DAL
{
    /// <summary>
    /// 商品数据访问操作类
    /// </summary>
    public class GoodsDAL
    {
        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Insert(Goods entity)
        {
            string sql = "insert into [goods]([name],[spec],[price]) values(@name,@spec,@price)";
            OleDbParameter[] param = new OleDbParameter[]{
                new OleDbParameter("@name",entity.Name),new OleDbParameter("@spec",entity.Spec),
                new OleDbParameter("@price",entity.Price)
            };
            return DataSourceTransactionManager.ExecuteNonQuery(sql,param);
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public int Update(Goods entity)
        {
            string sql = "update [goods] set [name]=@name,[spec]=@spec,[price]=@price where [id]=@id";
            OleDbParameter[] param = new OleDbParameter[]{
                new OleDbParameter("@name",entity.Name),new OleDbParameter("@spec",entity.Spec),
                new OleDbParameter("@price",entity.Price),new OleDbParameter("@id",entity.ID)
            };
            return DataSourceTransactionManager.ExecuteNonQuery(sql, param);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            string sql = "delete from [goods] where [id]=@id";
            OleDbParameter[] param = new OleDbParameter[]{
                new OleDbParameter("@id",id)
            };
            return DataSourceTransactionManager.ExecuteNonQuery(sql, param);
        }

        /// <summary>
        /// 获得商品列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetGoodsList()
        {
            string sql = "select * from [goods]";
            DataSet ds=DataSourceTransactionManager.ExecuteDataSet(sql);
            if (ds != null && ds.Tables.Count == 1)
                return ds.Tables[0];
            return null;
        }

        /// <summary>
        /// 查找某一个商品
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Goods Find(int id)
        {
            string sql = "select * from [goods] where [id]=@id";
            OleDbParameter[] param = new OleDbParameter[]{
                new OleDbParameter("@id",id)
            };
            OleDbDataReader mydr = DataSourceTransactionManager.ExecuteReader(sql, param);
            Goods entity=null;
            if (mydr != null)
            {
                if (mydr.Read())
                {
                    entity = new Goods();
                    entity.ID = id;
                    entity.Name = mydr["name"].ToString();
                    entity.Spec = mydr["spec"].ToString();
                    entity.Price =(float)Convert.ToDouble(mydr["price"]);
                }
            }
            return entity;
        }
    }
}
